﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Security.Cryptography;
using System.Data;
using System.Text.RegularExpressions;
using System.ComponentModel;
using System.Windows.Forms;
using System.Data.SqlClient;


namespace BoletoElectronicoDesktop
{


    public static class ClienteDAO
    {
        //devuelve la cantidad de clientes que tiene ese dni
        //si no existe devuelve cero
        public static int ExisteCliente(string tipoDni,string dni)
        {
            
            SqlConnection connection = new SqlConnection(Funciones.connectionString);
            SqlCommand command = new SqlCommand();
            connection.Open();
            command.Connection = connection;
            command.CommandType = System.Data.CommandType.StoredProcedure;
            command.CommandText = "[SELECTASTERISCO].Cliente_Cantidad_DNI";
            command.CommandTimeout = 10;
            command.Parameters.AddWithValue("@tipo_documento", Convert.ToInt32(tipoDni));
            command.Parameters.AddWithValue("@numero_documento", Convert.ToInt32(dni));
            SqlDataReader reader = command.ExecuteReader();
            DataTable dataTable = new DataTable();
            dataTable.Locale = System.Globalization.CultureInfo.InvariantCulture;
            dataTable.Load(reader);
            connection.Close();
            return Convert.ToInt32(dataTable.Rows[0][0]);

        }
        //inserta el cliente en la bd
        public static void InsertarCliente(string apellido, string nombre, string tipoDni, string dniNro, string mail, string telefono, string calle, string numero, string piso, string depto, string provincia)
        {
           //verifico los parámetros que pueden ser NULL y convierto los string


             SqlConnection connection = new SqlConnection(Funciones.connectionString);
             SqlCommand command = new SqlCommand();
             connection.Open();
             command.Connection = connection;
             command.CommandType = System.Data.CommandType.StoredProcedure;
             command.CommandText = "[SELECTASTERISCO].Cliente_Insercion";
             command.CommandTimeout = 10;
             command.Parameters.AddWithValue("@tipo_documento", tipoDni);
             command.Parameters.AddWithValue("@numero_documento", dniNro);
             command.Parameters.AddWithValue("@nombre", nombre);
             command.Parameters.AddWithValue("@apellido", apellido);
             command.Parameters.AddWithValue("@mail", mail);
             command.Parameters.AddWithValue("@telefono", telefono);
             command.Parameters.AddWithValue("@calle", calle);
             command.Parameters.AddWithValue("@numero_calle ", numero);
             if (string.Compare(piso, "") != 0)
             {
                 command.Parameters.AddWithValue("@piso", piso);
             }
             if (string.Compare(depto, "") != 0)
             {
                 command.Parameters.AddWithValue("@departamento ", depto);
             }
             command.Parameters.AddWithValue("@provincia ",provincia);

             SqlDataReader reader = command.ExecuteReader();
             connection.Close();
  
   
    }
        //obtiene los tipos de dni existentes 
       public static DataTable ObtenerDnis()
        {
     
            SqlConnection connection = new SqlConnection(Funciones.connectionString);
            SqlCommand command = new SqlCommand();
            connection.Open();
            command.Connection = connection;
            command.CommandType = System.Data.CommandType.StoredProcedure;
            command.CommandText = "[SELECTASTERISCO].Dni_Filtro";
            command.CommandTimeout = 10;
            SqlDataReader reader = command.ExecuteReader();
            DataTable dataTable = new DataTable();
            dataTable.Locale = System.Globalization.CultureInfo.InvariantCulture;
            dataTable.Load(reader);
            connection.Close();
            return dataTable;
        }

        //filtra los clientes que cumplen la condicion
       public static DataTable FiltrarClientes(string apellido, string nombre, string provincia, string tipodoc, string numdoc)
       {
           
           SqlConnection connection = new SqlConnection(Funciones.connectionString);
           SqlCommand command = new SqlCommand();
           connection.Open();
           command.Connection = connection;
           command.CommandType = System.Data.CommandType.StoredProcedure;
           command.CommandText = "[SELECTASTERISCO].Cliente_Filtro";
           command.CommandTimeout = 10;
           //construyo los parámetros 
           if (string.Compare(apellido, "") != 0)
           {
               command.Parameters.AddWithValue("@apellido ", apellido);
           }
           if (string.Compare(nombre, "") != 0)
           {
               command.Parameters.AddWithValue("@nombre ", nombre);
           }
           if (string.Compare(provincia, "") != 0)
           {
               command.Parameters.AddWithValue("@provincia ", provincia);
           }
           if (string.Compare(tipodoc, "") != 0)
           {
               command.Parameters.AddWithValue("@tipo_documento ",tipodoc);
           }
           if (string.Compare(numdoc, "") != 0)
           {
               command.Parameters.AddWithValue("@numero_documento ", numdoc);
           }
           SqlDataReader reader = command.ExecuteReader();
           DataTable dataTable = new DataTable();
           dataTable.Locale = System.Globalization.CultureInfo.InvariantCulture;
           dataTable.Load(reader);
           connection.Close();
           return dataTable;
       }

        //modifica los datos de un cliente
       public static void ModificarCliente(string apellido, string nombre, string tipoDni, string dni, string mail, string telefono, string calle, string numero, string piso, string depto, string provincia, string habilitado)
       {

           SqlConnection connection = new SqlConnection(Funciones.connectionString);
           SqlCommand command = new SqlCommand();
           connection.Open();
           command.Connection = connection;
           command.CommandType = System.Data.CommandType.StoredProcedure;
           command.CommandText = "[SELECTASTERISCO].Cliente_Modificacion";
           command.Parameters.AddWithValue("@tipo_documento", tipoDni);
           command.Parameters.AddWithValue("@numero_documento", dni);
           command.Parameters.AddWithValue("@apellido", apellido);
           command.Parameters.AddWithValue("@nombre", nombre);
           command.Parameters.AddWithValue("@mail", mail);
           command.Parameters.AddWithValue("@telefono", telefono);
           command.Parameters.AddWithValue("@calle", calle);
           command.Parameters.AddWithValue("@numero_calle ",numero);
           command.Parameters.AddWithValue("@provincia ", provincia);
           command.Parameters.AddWithValue("@habilitado ", habilitado);
           if (string.Compare(piso, "") != 0)
           {
               command.Parameters.AddWithValue("@piso ", piso);
           }
           if (string.Compare(depto, "") != 0)
           {
               command.Parameters.AddWithValue("@departamento ", depto);
           }
           command.CommandTimeout = 10;
           SqlDataReader reader = command.ExecuteReader();
           connection.Close();
          
       }

       //filtra los clientes que cumplen la condicion
       public static DataTable FiltrarClientesHabilitados(string apellido, string nombre, string provincia, string tipodoc, string numdoc)
       {
           SqlConnection connection = new SqlConnection(Funciones.connectionString);
           SqlCommand command = new SqlCommand();
           connection.Open();
           command.Connection = connection;
           command.CommandType = System.Data.CommandType.StoredProcedure;
           command.CommandText = "[SELECTASTERISCO].Cliente_Filtro";
           command.CommandTimeout = 10;
           //construyo los parámetros 
           if (string.Compare(apellido, "") != 0)
           {
               command.Parameters.AddWithValue("@apellido ", apellido);
           }
           if (string.Compare(nombre, "") != 0)
           {
               command.Parameters.AddWithValue("@nombre ", nombre);
           }
           if (string.Compare(provincia, "") != 0)
           {
               command.Parameters.AddWithValue("@provincia ", provincia);
           }
           if (string.Compare(tipodoc, "") != 0)
           {
               command.Parameters.AddWithValue("@tipo_documento ", tipodoc);
           }
           if (string.Compare(numdoc, "") != 0)
           {
               command.Parameters.AddWithValue("@numero_documento ", numdoc);
           }
           command.Parameters.AddWithValue("@habilitado ", "True");
           SqlDataReader reader = command.ExecuteReader();
           DataTable dataTable = new DataTable();
           dataTable.Locale = System.Globalization.CultureInfo.InvariantCulture;
           dataTable.Load(reader);
           connection.Close();
           return dataTable;

           
       }
       //modifica los datos de un cliente
       public static void InhabilitarCliente(string id)
       {
           SqlConnection connection = new SqlConnection(Funciones.connectionString);
           SqlCommand command = new SqlCommand();
           connection.Open();
           command.Connection = connection;
           command.CommandType = System.Data.CommandType.StoredProcedure;
           command.CommandText = "[SELECTASTERISCO].Cliente_Dar_Baja";
           command.Parameters.AddWithValue("@id",id);
           command.CommandTimeout = 10;
           SqlDataReader reader = command.ExecuteReader();
           connection.Close();
       }
    }











}
